#codeing = utf-8
#!/usr/bin/python3

import os
import sys

app_name = 'price analyse'
app_softverision = 111

def file_del(filepathname): #删除文件 filepathname-待删除的文件路径及名称
  try:
    os.remove(filepathname)
  except OSError as e:
    print(f"[错误] {filepathname}，错误信息: {e}")
    pass

def file_filetypefind(dir, filetype='o'): #按文件类型查找 匹配的文件 返回文件列表
  list_filename = [] # 存储筛选过的文件名
  for dir_path, dir_folder, filenames in os.walk(dir):
    for findex in filenames:
      # 检查文件后缀是否为'.bin'
        if findex.endswith(('.'+ filetype)):
            # 构建完整的文件路径
            full_file_path = os.path.join(dir_path, findex)
            # 将文件路径添加到列表中
            list_filename.append(full_file_path)
  return list_filename

def run():
  import tkinter as tk
  from tkinter import filedialog
  import openpyxl as pyxl
  import random
  from datetime import datetime
  import requests
  import json

  print('>', 'softverision: ', app_name + ' ' + str(app_softverision))

  # 判断是否有本地的配置文件
  json_pathname = os.getcwd() + '\\tools_price_analyse.json'
  if os.path.isfile(json_pathname):
    with open(json_pathname, 'r', encoding='utf8') as json_file:
      dict_json = json.load(json_file)
  else:
    print('>')
    print('>', '[错误]:', json_pathname, '配置文件未找到')
    print('>')
    os.system('pause\n')
    sys.exit(0)
  #   # 读取云端 配置文件
  #   json_url = 'https://gitee.com/Bite108/tools_price_analyse/raw/master/tools_price_analyse_config.json'
  #   response = requests.get(json_url)
  #   dict_json = response.json()

  root = tk.Tk()
  root.withdraw()  # 隐藏主窗口
  print('\n>','请选择excel文件')
  filetypes = [('Excel Files', '*.xlsx'), ('Excel 2007 Files', '*.xls'), ('All Files', '*')]
  pathname_excel = filedialog.askopenfilename(title='请选择excel文件', filetypes=filetypes)
  if pathname_excel == '':
    print('>')
    print('>', '[错误]:', '未选择文件')
    print('>')
    os.system('pause\n')
    sys.exit(0)
  print('>', '数据文件为', f'[{pathname_excel}]')

  #excel写入文件名
  dir_excel_save = os.path.dirname(pathname_excel)  + '/analyse/'

  workbook = pyxl.load_workbook(pathname_excel)
  sheet_opt = workbook[workbook.sheetnames[0]]
  sheet_opt_row_max = sheet_opt.max_row + 1

  dict_param = {}
  for col in range(1, sheet_opt.max_column):
    for param_index in range (len(dict_json['dispparam'])):
      str_field = dict_json['dispparam'][param_index]
      if str(sheet_opt.cell(1, col).value)==str_field:
        dict_param[str_field] = col

  # 检查参数是否完整
  for index in dict_json['dispparam']:
    if (index in dict_param)==False:
      print('>')
      print('>', '[错误]:', 'Excel文件数据不合规，未找到数据字段 -> ', index)
      print('>')
      os.system('pause\n')
      sys.exit(0)

  # 检查文件夹是否存在
  if not os.path.exists(dir_excel_save):
    os.makedirs(dir_excel_save) # 文件夹不存在，创建文件夹

  # 批量删除之前的文件 xlsx png
  list_filepathname_del=[]
  list_filepathname_del.extend(file_filetypefind(dir_excel_save, 'xlsx'))
  list_filepathname_del.extend(file_filetypefind(dir_excel_save, 'png'))

  for index in list_filepathname_del:
    file_del(index)

  # 查找用户信息
  list_bill_notfind = [] # 未找到的用户列表
  for memindex in range (len(dict_json['member'])):
    # print('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
    # print('>', f"[{memindex+1}]", dict_json['member'][memindex]['mob'], dict_json['member'][memindex]['姓名'])
    workbook = pyxl.load_workbook(pathname_excel)
    sheet_opt = workbook[workbook.sheetnames[0]]
    list_bill = []

    for row in range (1, sheet_opt_row_max):
      if sheet_opt.cell(row, (dict_param['mob'])).value==dict_json['member'][memindex]['mob']:
        result = {}
        for param_index in range (len(dict_json['dispparam'])):
          str_field = dict_json['dispparam'][param_index]
          result[str_field] = str(sheet_opt.cell(row, (dict_param[str_field])).value)
        list_bill.append(result)

    #计算实付金额
    price = 0
    dict_tmp = {}
    if list_bill == []:
      dict_tmp['姓名'] = dict_json['member'][memindex]['姓名']
      dict_tmp['电话'] = dict_json['member'][memindex]['mob']
      list_bill_notfind.append(dict_tmp)
      # print('> [错误]： 没有找到【' + dict_json['member'][memindex]['姓名'] + '】的用餐记录\n')
      # os.system('pause\n')
    else:
      for line in list_bill:
        price = price + float(line['实付'])
        # guest_mob = line['mob']

      price = round(price, 2)
      str_comments = random.choice(dict_json['comments'])

      workbook = pyxl.Workbook()
      sheet_opt = workbook.active
      #定义列标题
      for col, header in enumerate(dict_json['dispparam'], 1):
        sheet_opt.cell(1, col, header)
      #写入数据
      for row,dictindex in enumerate(list_bill, 2):
        for col, (key, value) in enumerate(dictindex.items(), 1):
          sheet_opt.cell(row, col, value)

      #设置列宽
      list_excel_row = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q']
      for i in range(0, len(dict_json['dispparam'])):
        sheet_opt.column_dimensions[list_excel_row[i] ].width = 20

      row = row + 1
      row = row + 1
      str_text = f'[鳄宝盘点] 实付总金额 {price}'
      sheet_opt.cell(row, 1, str_text)

      row = row + 1
      str_text = f'[鳄宝提醒] {str_comments}'
      sheet_opt.cell(row, 1, str_text)

      #检测该excel文件是否存在 存在就删除
      savename = dir_excel_save + dict_json['member'][memindex]['姓名'] + '_' + str(dict_json['member'][memindex]['mob'])
      # if os.path.isfile(savename):
      #   savename = (dir_excel_save + dict_json['member'][memindex]['姓名'] + datetime.now().strftime("%Y%m%d%H%M%S"))
      savename_excel = savename+'.xlsx'
      try:
        workbook.save(savename_excel)
      except Exception as e:
        workbook.close
        pass
      finally:
        workbook.close

      # str_guest = "{'mob':{}, '姓名':'{}'}".format(guest_mob, dict_json['member'][memindex]['姓名'])
      # print('>', str_guest)
      # {"客户ID":77, "姓名":"刘国红"}
      # print('>', '"mob":',guest_mob, '"姓名":',dict_json['member'][memindex]['姓名'])
      print('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
      print('>', f"[{memindex+1}]", dict_json['member'][memindex]['mob'], dict_json['member'][memindex]['姓名'])
      print('>', '[鳄宝盘点]', '实付总金额', price, '元')

  print('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
  print('>', '[鳄宝提醒]', '数据解析完成')
  print('>', '[鳄宝提醒]', '数据保存路径为', dir_excel_save)
  print('>')

  if list_bill_notfind != []:
    print('> [提醒]： 以下员工，未匹配到用餐记录')
    cnt = 0
    for index in list_bill_notfind:
      cnt = cnt + 1
      print('>', f'[{cnt}]', index)

  print('>')
  os.system('pause')

# 下载文件url 另存为 savepathname
def download_url(url, savepathname):
  import wget

  if os.path.exists(savepathname) == True: #查找本地文件当前是否有这个文件
    try:
      os.remove(savepathname)
    except OSError as e:
      print('>', f"[错误] {savepathname}，错误信息: {e}")

  wget.download(url, savepathname)

def writerestartcmd(new_name,old_name):
  b = open("upgrade.bat",'w')
  TempList = "@echo off\n"
  TempList += "if not exist " + new_name + " exit \n"  #判断是否有新版本的程序，没有就退出更新。
  TempList += "echo 正在更新至最新版本...\n"
  TempList += "timeout /t 3 /nobreak\n"  #等待3秒
  TempList += "del " + old_name + "\n" #删除旧程序
  TempList += "copy  " + new_name + " " + old_name + '\n' #复制新版本程序
  TempList += "del " + new_name + "\n" #删除下载缓存程序
  TempList += "echo 更新完成，正在启动...\n"
  TempList += "timeout /t 3 /nobreak\n"
  TempList += "start  " + old_name+"\n"
  TempList += "echo. & echo Finished.\n"
  TempList += "exit\n"
  b.write(TempList)
  b.close()
  os.system('start upgrade.bat')  #显示cmd窗口

def checkupdata():
  import urllib.request
  import re

  #新程序启动时，删除旧程序制造的脚本
  if os.path.isfile("upgrade.bat"):
    os.remove("upgrade.bat")
  
  if os.path.isfile("latest.exe"):
    os.remove("latest.exe")

  # 获取版本信息 有更新则执行
  itemname = 'tools_price_analyse'
  url_latest = "https://gitee.com/Bite108/{}/releases/latest".format(itemname)

  print('>', 'check for software updates')
  #html用decode(‘utf-8’)进行解码，由bytes变成string，py3的urlopen返回的不是string是bytes
  try:
    res = urllib.request.urlopen(url_latest).read().decode('utf-8')
  except:
    print('>', 'the ethernet is not connection, skip check software verision')
    return False
  str_filier = f'<a href="/Bite108/{itemname}/releases/download/'
  str_filier = str_filier + '(.*?)">'
  url_app_tmp = re.compile(str_filier).findall(res)
  url_download = f'https://gitee.com/Bite108/{itemname}/releases/download/' + url_app_tmp[0]

  verision_latest = url_app_tmp[0].split("/")
  print('>', 'curr:', str(app_softverision), '| latest:', verision_latest[0])

  if app_softverision >= int(verision_latest[0]):
    pass
    print('>', 'curr is latest')
  else:
    print('>', 'start download the latest')
    workpath = os.getcwd() #获取工作路径
    pathname_updata = workpath + '\\latest.exe'
    pathname_save = workpath + f'\\{itemname}.exe'
    download_url(url_download, pathname_updata)
    writerestartcmd(pathname_updata, pathname_save)
    sys.exit(0)

if __name__ == '__main__':
  print('\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
  checkupdata()
  run()